package com.project.dao.impl;

import com.project.bean.CutPageBean;
import com.project.bean.ProductBean;
import com.project.dao.IProductDao;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

@Repository
public class ProductDaoImpl extends BaseDao implements IProductDao {
    @Override
    public CutPageBean<ProductBean> findByItem(int pageNo, String name, LocalDate startDate, LocalDate endDate) {
        CutPageBean<ProductBean> cut = new CutPageBean<>();
        this.setConnection();
//        String sql = "select * from t_products where 1=1 ";
//        String dynaSql="";
//        if (name!=null&&name.length()!=0){
//            dynaSql+="and p_name like '%"+name+"%' ";
//        }
//        if (startDate!=null){
//            dynaSql+="and p_createDate >= '"+startDate+"' ";
//        }
//        if (endDate!=null){
//            dynaSql+="and p_createDate <= '"+endDate+"' ";
//        }
//        try {
//            ps = con.prepareStatement(sql+dynaSql+" limit ?,?  ");
//            ps.setObject(1,(pageNo-1)*CutPageBean.PAGESIZE);
//            ps.setObject(2,CutPageBean.PAGESIZE);
        String dynaSql = " where 1=1 ";
        List valueList = new ArrayList<>();
        if (name != null && name.length() != 0){
            dynaSql += "and p_name like ? ";
            valueList.add("%"+name+"%");
        }
        if (startDate != null){
            dynaSql += "and p_createDate >= ? ";
            valueList.add(startDate);
        }
        if (endDate != null){
            dynaSql += "and p_createDate <= ? ";
            valueList.add(endDate);
        }
        try {
            ps = con.prepareStatement("select * from t_products "+dynaSql+" limit ?,?");
            for (int i = 0; i < valueList.size(); i++) {
                ps.setObject(i+1,valueList.get(i));
            }
            ps.setObject(valueList.size() + 1,(pageNo-1)*CutPageBean.PAGESIZE);
            ps.setObject(valueList.size() + 2,CutPageBean.PAGESIZE);

            rs = ps.executeQuery();
            while (rs.next()){
                cut.getList().add(this.getUser());
            }

            ps = con.prepareStatement("select count(*) c from t_products "+dynaSql);
            for (int i = 0; i < valueList.size(); i++) {
                ps.setObject(i+1,valueList.get(i));
            }

            rs = ps.executeQuery();
            while (rs.next()){
                cut.setCount(rs.getInt("c"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            this.closeConnection();
        }
        return cut;
    }

    @Override
    public ProductBean findById(Integer id) {
        this.setConnection();
        ProductBean productBean = new ProductBean();
        try {
            ps=con.prepareStatement("select * from t_products where pk_productsId=?");
            ps.setObject(1,id);
            rs = ps.executeQuery();
            while (rs.next()){
                productBean=this.getUser(rs);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            this.closeConnection();
        }

        return productBean;
    }

    @Override
    public void update(Integer id, Integer price) {
     this.setConnection();
        try {
            ps = con.prepareStatement("update t_products set p_price=? where pk_productsId=?");
            ps.setObject(1,price);
            ps.setObject(2,id);
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            this.closeConnection();
        }

    }

    @Override
    public void del(Integer id) {
        this.setConnection();
        try {
            ps = con.prepareStatement("delete from t_products where pk_productsId=?");
            ps.setObject(1,id);
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            this.closeConnection();
        }
    }

    @Override
    public void add(ProductBean productBean) {
      this.setConnection();
        try {
            ps = con.prepareStatement("insert into t_products(p_name,p_createDate,p_price)"+"values(?,?,?)");
            ps.setObject(1,productBean.getName());
            ps.setObject(2,productBean.getCreateDate());
            ps.setObject(3,productBean.getPrice());
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            this.closeConnection();
        }
    }

    public ProductBean getUser(){
        ProductBean order = new ProductBean();
        try {
            order.setName(rs.getString("p_name"));
            order.setId(rs.getInt("pk_productsId"));
            order.setPrice(rs.getInt("p_price"));
            String bir = rs.getString("p_createDate");
            order.setCreateDate(bir!=null? LocalDate.parse(bir):null);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return order;
    }

    public static void main(String[] args) {
//        IProductDao dao = new ProductDaoImpl();
////        dao.add(new ProductBean("电风扇",LocalDate.parse("2022-06-12"),120));
//        dao.del(11);
    }

}
